﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;

namespace JunQuanPrinter
{
    public class CSQLite : IDisposable
    {
        //public string m_LastError = null;
        private string dbPath = "";
        private SQLiteConnection sqConn = null;
        private SQLiteCommand sqCmd = null;
        private SQLiteTransaction transaction = null;
        private bool m_Result = false;
        private static CSQLite m_Obj = null;
        public static CSQLite getClass()
        {
            if(m_Obj == null)
            {
                m_Obj = new CSQLite();
            }
            return m_Obj;
        }

        public bool Result
        {
            get { return m_Result; }
        }
        /// <summary>
        /// 不允许通过该方式构造此类
        /// </summary>
        private CSQLite() {
            m_Obj = this;
        }
        /// <summary>
        /// 打开数据库
        /// </summary>
        /// <param name="dbPath">数据库路径</param>
        public CSQLite(string dbPath)
        {
            this.dbPath = dbPath;
            m_Obj = this;
        }

        ~CSQLite()
        {
            //Close();
        }

        #region  打开与关闭
        public bool Open()
        {
            try
            {
                m_Result = false;
                if (sqConn == null)
                {
                    //在打开数据库时，会判断数据库是否存在，如果不存在，则在当前目录下创建一个
                    sqConn = new SQLiteConnection("Data Source=" + dbPath + ";Pooling=true;FailIfMissing=false");
                    sqCmd = new SQLiteCommand();
                    sqCmd.Connection = sqConn;
                }
                if (sqConn.State == ConnectionState.Closed)
                {
                    bool dbExsit = System.IO.File.Exists(dbPath);
                    sqConn.Open();
                    /// 如果数据库不存在，sqlite会创建一个空的数据库，在此创建一个无用的表，填充数据库
                    if (!dbExsit)
                    {
                        ExecuteNonQuery("create table Liang ( id nvarchar(1) ) ");
                    }
                }
                m_Result = true;
                return true;
            }
            catch (System.Exception ex)
            {
                //m_LastError = ex.Message;
                //return false;
                throw ex;
            }
        }
        public void Dispose()
        {
            Close();
        }
        public void Close()
        {
            if (sqConn != null)
            {
                if (sqConn.State == ConnectionState.Open)
                {
                    sqConn.Close();
                    sqConn = null;
                    sqCmd = null;
                }
            }
            System.Data.SQLite.SQLiteConnection.ClearAllPools();
        }
        #endregion
        /// <summary>
        ///  执行SQL命令，返回影响行数
        /// </summary>
        /// <param name="sqlCmd">查询语句</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sqlCmd)
        {
            //m_LastError = null;
            m_Result = false;
            int count = 0;
            try
            {
                //Open();
                sqCmd.CommandText = sqlCmd;
                count = sqCmd.ExecuteNonQuery();
                m_Result = true;
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
              // Close();
            }
            return count;
        }
        /// <summary>
        /// 执行SQL命令,并返回Read,Read使用完毕,必须关闭
        /// </summary>
        /// <param name="sqlCmd"></param>
        /// <returns></returns>
        public SQLiteDataReader ExecuteQuery(string sqlCmd)
        {
            //m_LastError = null;
            m_Result = false;
            try
            {
                sqCmd.CommandText = sqlCmd;
                SQLiteDataReader read = sqCmd.ExecuteReader();
                m_Result = true;
                return read;
            }
            catch (System.Exception ex)
            {
                throw ex;
                //m_LastError = ex.Message;
                //return null;
            }
        }
        /// <summary>
        /// 执行SQL命令,并返回DataTable
        /// </summary>
        /// <param name="sqlCmd"></param>
        /// <returns></returns>
        public DataTable ExecuteQueryTable(string sqlCmd)
        {
            //m_LastError = null;
            m_Result = false;
            //dt = new DataTable("liang");
            DataTable dt = new DataTable();
            try
            {
               // Open();
                // 执行查询命令
                SQLiteDataReader read = ExecuteQuery(sqlCmd);
                if (m_Result)
                {
                    m_Result = false;
                    if (read == null)
                    {
                        //m_LastError = "sqlite error:未查询到数据!";
                        //return false;
                    }
                    /// 添充表
                    for (int i = 0; i < read.FieldCount; i++)
                    {

                        dt.Columns.Add(new DataColumn(read.GetName(i))); // i.ToString()));
                    }
                    while (read.Read())
                    {
                        DataRow row = dt.NewRow();
                        for (int i = 0; i < read.FieldCount; i++)
                        {
                            row[i] = read.GetValue(i).ToString();
                        }
                        dt.Rows.Add(row);
                    }
                    read.Close();
                }
                return dt;
            }
            catch (System.Exception ex)
            {
                throw ex;
                //m_LastError = ex.Message;
                //return dt;
            }
            finally
            {
                //Close();
            }
        }
        /// <summary>
        /// 执行SQL命令,并返回第一行记录的第一列值
        /// </summary>
        /// <param name="sqlCmd"></param>
        /// <returns></returns>
        public object ExecuteScalar(string sqlCmd)
        {
            //m_LastError = null;
            m_Result = false;
            try
            {
                sqCmd.CommandText = sqlCmd;
                object ob = sqCmd.ExecuteScalar();
                if (ob != null)
                {
                    m_Result = true;
                }
                else
                {
                    //m_LastError = "sqlite error:未查询到数据";
                }
                return ob;
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }
        #region 事务操作
        public void BeginTransaction()
        {
            transaction = sqConn.BeginTransaction();
        }
        public void Commit()
        {
            transaction.Commit();
        }
        public void Rollback()
        {
            transaction.Rollback();
        }
        #endregion
    }
}